import os

from openpyxl import load_workbook, Workbook
from openpyxl.utils import get_column_letter

from utils.excel import copy_worksheet_pro,copy_cell_pro
from utils.file import get_sort_file_list,get_file_list

def multiple_excel_merge_multiple_sheet(folder, key=None, reverse=None, callback=None):
    """
    将多个工作部合并为（同一工作簿下的）多张工作表
    :param folder: 存放excel的文件夹
    :param callback:
    :param reverse:
    :param key:
    """
    excel_list = get_sort_file_list(folder, ".xlsx", key, reverse)
    new_wb = Workbook()
    for excel_index,excel_name in enumerate(excel_list):
        old_ws = load_workbook(os.path.join(folder,excel_name)).active
        new_ws = new_wb.create_sheet(os.path.splitext(excel_name)[0])   # 以原工作簿名做新工作表名
        copy_worksheet_pro(old_ws,new_ws)
        print("In Progress:  读取%s成功" % excel_name)
        if callback is not None:
            callback(excel_index, len(excel_list), excel_name)
    new_wb.remove(new_wb.active)    # 删除默认工作表
    return new_wb

def multiple_excel_merge_single_sheet(folder, start_row=2, callback=None):
    """
    将多个工作簿合并为（同一工作簿下的）单张工作表
    :param folder: 存放excel的文件夹
    :param start_row: 起始行，从第几行开始合并
    :param callback:
    :param reverse:
    :param key:
    """
    excel_list = get_file_list(folder,".xlsx")
    new_wb = load_workbook(os.path.join(folder,excel_list[0]))  # 第一个表为基础做新表
    new_ws = new_wb.active
    new_column_widths = []  # 列宽
    for column_index in range(1, new_ws.max_column + 1):    # 初始化列宽
        new_column_widths.append(new_ws.column_dimensions[get_column_letter(column_index)].width)
    if callback is not None:
        callback(0, len(excel_list), excel_list[0])
    print("In Progress:  读取%s成功" % excel_list[0])
    current_row = new_ws.max_row + 1  # 当前写入行数，为最大行的下一行
    for excel_index,excel_name in enumerate(excel_list[1:]):
        old_ws = load_workbook(os.path.join(folder,excel_name)).active
        for row_index, rows in enumerate(old_ws.iter_rows(min_row=start_row)):
            for col_index, old_cell in enumerate(rows):
                new_cell = new_ws.cell(current_row, col_index + 1)
                copy_cell_pro(old_cell, new_cell)   # 复制单元格
            old_row_height = old_ws.row_dimensions[row_index + start_row].height
            if old_row_height is not None:
                new_ws.row_dimensions[current_row].height = old_row_height  # 复制行高
            current_row += 1  # 行数下移
        for column_index in range(1, new_ws.max_column + 1):    # 获取最大列宽
            old_column_width = old_ws.column_dimensions[get_column_letter(column_index)].width
            if old_column_width > new_column_widths[column_index-1]:
                new_column_widths[column_index-1] = old_column_width
        print("In Progress:  读取%s成功" % excel_name)
        if callback is not None:
            callback(excel_index+1, len(excel_list), excel_name)
    for column_index in range(1, new_ws.max_column + 1):    # 设置列宽
        new_ws.column_dimensions[get_column_letter(column_index)].width = new_column_widths[column_index-1]
    return new_wb

def multiple_sheet_merge_single_sheet(file,start_row=2,callback=None):
    """
    将多个工作表合并为（同一工作簿下的）单张工作表
    :param file: excel文件路径
    :param start_row: 起始行，从第几行开始合并
    """
    # 新建工作簿，复制第一张表，写入其他张
    new_wb = Workbook()
    new_ws = new_wb.active
    old_wb = load_workbook(file)
    old_ws = old_wb.active
    copy_worksheet_pro(old_ws,new_ws)  # 第一个工作表为基础做新工作表
    new_column_widths = []  # 列宽
    for column_index in range(1, new_ws.max_column + 1):    # 初始化列宽
        new_column_widths.append(new_ws.column_dimensions[get_column_letter(column_index)].width)
    print("In Progress:  读取%s成功" % old_ws.title)
    if callback is not None:
        callback(0, len(old_wb.sheetnames), old_ws.title)
    current_row = new_ws.max_row + 1  # 当前写入行数，为最大行的下一行
    for sheet_index,sheet_name in enumerate(old_wb.sheetnames[1:]):
        old_ws = old_wb[sheet_name]
        for row_index, rows in enumerate(old_ws.iter_rows(min_row=start_row)):
            for col_index, old_cell in enumerate(rows):
                new_cell = new_ws.cell(current_row, col_index + 1)
                copy_cell_pro(old_cell, new_cell)  # 复制单元格
            old_row_height = old_ws.row_dimensions[row_index + start_row].height
            if old_row_height is not None:
                new_ws.row_dimensions[current_row].height = old_row_height  # 复制行高
            current_row += 1  # 行数下移
            for column_index in range(1, new_ws.max_column + 1):  # 获取最大列宽
                old_column_width = old_ws.column_dimensions[get_column_letter(column_index)].width
                if old_column_width > new_column_widths[column_index - 1]:
                    new_column_widths[column_index - 1] = old_column_width
        print("In Progress:  读取%s成功" % sheet_name)
        if callback is not None:
            callback(sheet_index+1, len(old_wb.sheetnames), sheet_name)
        for column_index in range(1, new_ws.max_column + 1):  # 设置列宽
            new_ws.column_dimensions[get_column_letter(column_index)].width = new_column_widths[column_index - 1]
    return new_wb